package JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public int add(Student s){
        List<Object> list=new ArrayList<>();
        list.add(s.getSname());
        list.add(s.getSex());
        list.add(s.getTelephone());
        list.add(s.getQq());
        list.add(s.getWechat());
        list.add(s.getGradeid());
        list.add(s.getEnterdate());
        list.add(s.getFromcity());
        list.add(s.getBirthday());
        list.add(s.getAddress());
        list.add(s.getPass());
        list.add(s.getIdcardpic());
        String sql="insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterday,fromcity,birthday,address,pass,idcardpic) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        return DBUtil.executeUpdate(sql,list);
    }

    public int del(long stuno){
        List<Object> list = new ArrayList<>();
        list.add(stuno);
        String sql = "Delete from s_student where id=?";
        return DBUtil.executeUpdate(sql, list);
    }

    public int update(Student s){
        List<Object> list=new ArrayList<>();
        list.add(s.getSname());
        list.add(s.getSex());
        list.add(s.getTelephone());
        list.add(s.getQq());
        list.add(s.getWechat());
        list.add(s.getGradeid());
        list.add(s.getEnterdate());
        list.add(s.getFromcity());
        list.add(s.getBirthday());
        list.add(s.getAddress());
        list.add(s.getPass());
        list.add(s.getIdcardpic());
        String sql = "UPDATE s_studen set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterday=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=? where stuno=" + s.getStuno();
        return DBUtil.executeUpdate(sql,list);
    }

    public List<Student> search(int pageNo,int pageSize){
        List<Student> studentList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from s_student order by id desc limit ?,?";
            st = connection.prepareStatement(sql);
            st.setInt(1, (pageNo - 1) * pageSize);
            st.setInt(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                studentList.add(new Student(rs.getInt("stuno"),rs.getString("sname"),rs.getString("sex"),
                        rs.getString("telephone"),rs.getString("qq"),rs.getString("wechat"),rs.getLong("gradeid"),
                        rs.getString("enterday"),rs.getString("fromcity"),rs.getString("birthday"),rs.getString("address"),
                        rs.getString("pass"),rs.getString("idcardpic")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, connection);
        }
        return studentList;
    }

}
